<?php
/** Error reporting */
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
date_default_timezone_set('PRC');
/** 引入PHPExcel */
require_once './phpexcel/Classes/PHPExcel.php';
require_once './phpexcel/Classes/PHPExcel/IOFactory.php';

class Excel
{
    private $objPHPExcel;
    private $pdo;
    
    public function __construct(){
        $this->objPHPExcel = new  PHPExcel();
        $this->pdo = new Mypdo();
       
    }
    
    public function downLoad(){
        $filename = '天猫主体账';
        if (ob_get_length()) ob_end_clean();//清除缓冲区,避免乱码
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="'.$filename.'.xls"');
        header('Cache-Control: max-age=0');
        // If you're serving to IE 9, then the following may be needed
        header('Cache-Control: max-age=1');
        
        // If you're serving to IE over SSL, then the following may be needed
        header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
        header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header('Pragma: public'); // HTTP/1.0
        
        $this->setDocInfo();
        $this->setTitle();
        $this->insertData();
        $this->setStyle();
        $objWriter = new PHPExcel_Writer_Excel2007($this->objPHPExcel);
        $objWriter->save('php://output');
    }
    
    private function setDocInfo(){
        $this->objPHPExcel->getProperties()->setCreator("zql")
        ->setLastModifiedBy("zql")
        ->setTitle("天猫主体账单")
        ->setSubject("天猫主体账单")
        ->setDescription("天猫主体账单")
        ->setKeywords("账单")
        ->setCategory("账单");
    }
    
    private function setTitle(){
        
        //根据excel坐标，添加数据
        $this->objPHPExcel->setActiveSheetIndex(0)
        ->setCellValue('A1', '交易日期')
        ->setCellValue('B1', '天猫订单号')
        ->setCellValue('C1', '支付宝流水号')
        ->setCellValue('D1', '刷卡方式')
        ->setCellValue('E1', '订单类型')
        ->setCellValue('F1', '客户编码')
        ->setCellValue('G1', '客户名称')
        ->setCellValue('H1', '收款金额')
        ->setCellValue('I1', '返点积分')//
        ->setCellValue('J1', '导购宝佣金')
        ->setCellValue('K1', '大额信用卡')
        ->setCellValue('L1', '花呗分期')
        ->setCellValue('M1', '淘宝客佣金')
        ->setCellValue('N1', '天猫佣金')
        ->setCellValue('O1', '其他服务费')
        ->setCellValue('P1', '费用合计')
        ->setCellValue('Q1', '支付宝余额')
        ->setCellValue('R1', '提现金额');
        
        
    }
    
    private function setStyle(){
        $cell_fill = array(
            'A1',
            'B1',
            'C1',
            'D1',
            'E1',
            'F1',
            'G1',
            'H1',
            'I1',
            'J1',
            'K1',
            'L1',
            'M1',
            'N1',
            'O1',
            'P1',
            'Q1',
            'R1',
        );
        foreach($cell_fill as $cell_fill_val){
            $cellstyle = $this->objPHPExcel->getActiveSheet()->getStyle($cell_fill_val);
        
            $cellstyle->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT)->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
            // font
            $cellstyle->getFont()->setSize(12)->setBold(true);
            // border
            $cellstyle->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN)->getColor()->setARGB('FFFF0000');
            $cellstyle->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN)->getColor()->setARGB('FFFF0000');
            $cellstyle->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN)->getColor()->setARGB('FFFF0000');
            $cellstyle->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN)->getColor()->setARGB('FFFF0000');
        }
        $this->objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(30);
        $this->objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(30);
        $this->objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(30);
        $this->objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(30);
        $this->objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(15);
        $this->objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(15);
        $this->objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(15);
        $this->objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(15);
        $this->objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(15);
        $this->objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(15);
        $this->objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(15);
        $this->objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(15);
        $this->objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(15);
        $this->objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(15);
        $this->objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(15);
        $this->objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
        $this->objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(10);
        $this->objPHPExcel->getActiveSheet()->getColumnDimension('Q')->setWidth(20);
        $this->objPHPExcel->getActiveSheet()->getColumnDimension('R')->setWidth(15);
        $this->objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(15);
        $this->objPHPExcel->getActiveSheet()->freezePane('A2');
    }
    
    private function getData(){
        $statement = "select * from tianmao_orders";
        return $this->pdo->PdoQueryAll($statement);
    }
    private function insertData(){
        $datas = $this->getData();
   
        $i = 2;
        foreach ($datas as $data) {
//             $this->objPHPExcel->getActiveSheet()
//                 ->setCellValue('A' . $i, $data['id'])
//                 ->getStyle('A' . $i)
//                 ->getAlignment()
//                 ->setHorizontal(PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $this->objPHPExcel->getActiveSheet()->setCellValue('A' . $i, $data['transaction_time']);
            $this->objPHPExcel->getActiveSheet()
            ->setCellValueExplicit('B' . $i, $data['tianmao_order_num'], PHPExcel_Cell_DataType::TYPE_STRING);
            $this->objPHPExcel->getActiveSheet()
            ->getStyle('B' . $i)
            ->getAlignment()
            ->setWrapText(true);
            $this->objPHPExcel->getActiveSheet()
            ->setCellValueExplicit('C' . $i, $data['zhifubao_water_num'], PHPExcel_Cell_DataType::TYPE_STRING);
            $this->objPHPExcel->getActiveSheet()
            ->getStyle('C' . $i)
            ->getAlignment()
            ->setWrapText(true);
            $this->objPHPExcel->getActiveSheet()->setCellValue('D' . $i, $data['type']);
            $this->objPHPExcel->getActiveSheet()->setCellValue('E' . $i, $data['order_type']);
            $this->objPHPExcel->getActiveSheet()->setCellValue('F' . $i, '');
            $this->objPHPExcel->getActiveSheet()->setCellValue('G' . $i, $data['client_name']);
            $this->objPHPExcel->getActiveSheet()->setCellValue('H' . $i, $data['in_money']);
            $this->objPHPExcel->getActiveSheet()->setCellValue('I' . $i, $data['integral']);
            $this->objPHPExcel->getActiveSheet()->setCellValue('J' . $i, $data['daogoubao']);
            $this->objPHPExcel->getActiveSheet()->setCellValue('K' . $i, $data['credit_card']);
            $this->objPHPExcel->getActiveSheet()->setCellValue('L' . $i, $data['huabei']);
            $this->objPHPExcel->getActiveSheet()->setCellValue('M' . $i, $data['taobao']);
            $this->objPHPExcel->getActiveSheet()->setCellValue('N' . $i, $data['tianmao_commission']);
            $this->objPHPExcel->getActiveSheet()->setCellValue('O' . $i, $data['other_service']);
            $this->objPHPExcel->getActiveSheet()->setCellValue('P' . $i, $data['expenses_sum']);
            $this->objPHPExcel->getActiveSheet()->setCellValue('Q' . $i, $data['account_money']);
            $this->objPHPExcel->getActiveSheet()->setCellValue('R' . $i, $data['presentation_money']);
           
            $i ++;
        }
    }
    
    /**
     * 读取excel表里的内容
     */
    public function getExcel($inputFileName)
    {   
        //$inputFileName = './file/1563956257.xlsx';
        date_default_timezone_set('PRC');
        // 读取excel文件
        try {
            $inputFileType = PHPExcel_IOFactory::identify($inputFileName);
            $objReader = PHPExcel_IOFactory::createReader($inputFileType);
            $objPHPExcel = $objReader->load($inputFileName);
        } catch(Exception $e) {
            die("加载文件发生错误：".pathinfo($inputFileName,PATHINFO_BASENAME).": ".$e->getMessage());
        }
     
        $sheet = $objPHPExcel->getSheet(0);
        $highestRow = $sheet->getHighestRow();
        $highestColumn = $sheet->getHighestColumn();
      
        $title = [
            0 => 'in_account_time',
            1 => 'zhifubao_order_num',
            2 => 'water_num',
            3 => 'ct_order',
            4 => 'type',
            5 => 'income',
            6 => 'expenditure',
            7 => 'money',
            8 => 'service_money',
            9 => 'source',
            10 => 'product',
            11 => 'client_account',
            12 => 'client_name',
            13 => 'goods_name',
            14 => 'remarks'
        ];
       $count = 0;
        // 获取一行的数据
        for ($row = 2; $row <= $highestRow; $row++){
            $list = [];
            // Read a row of data into an array
            $rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE);
            //这里得到的rowData都是一行的数据，得到数据后自行处理，我们这里只打出来看看效果

            unset($rowData[0][0]);
            unset($rowData[0][14]);
            $value = array_merge($rowData[0],[]);
            $list = array_combine($title, $value);

            $count += $this->pdo->Insert($list, 'zhifubao_account');
        }
       if ($count >= $highestRow-1){
           return true;
       }
       return false;
        //return $this->pdo->InsertAll($list, 'zhifubao_account');
     
    }
}


